Havish M Consulting
Customer Case Study · Transmission Projects · Planning
Power Transmission × Havish M Consulting

Shift one stretch. The whole line re-plans itself.

A VBA schedule engine for a leading power transmission company's line projects. A field delay in foundation, erection or stringing now ripples through every downstream stretch in seconds, and planners see the result on a Gantt chart inside Excel.

ClientLeading Power Transmission Company FunctionProject Planning & Controls ThemeSchedule Automation
Time to re-forecast after a field change
0min
▼ from about 12 hours of manual edits
Schedule levers in one form
0levers
14 stretches × 3 stages × 4 levers
Re-forecasts per month
0a month
▲ from one monthly update
Earlier warning of finish-date slippage
0weeks
▲ seen while it could still be recovered

Figures indicative; validated metrics available on request.

The Challenge

Hundreds of levers, and every one moved by hand

The company planned its transmission lines in Microsoft Project and TILOS. Every change from the field had to reach the schedule at one of three stages (foundation, erection or stringing) and then flow on to every stretch that depended on it.

Two tools, one field

The baseline lived in Microsoft Project and the time–location view in TILOS. Field teams reported in neither, so every update was re-keyed twice.

Three stages, one chain

Erection waits for foundations to cure, and stringing waits for the whole stretch to be erected. One late foundation pushes all three stages downstream.

Too many levers

Every stretch had a start, a duration, a shift and a factor at each stage. With 14 stretches in a package that is 168 levers, all linked.

Re-plans arrived late

Monsoon, right-of-way and crossing approvals changed the plan every week. A manual re-plan took about a day, so the schedule was always behind the site.

HMC Approach

From two planning tools to one engine in Excel

Built with the company's planning team, keeping Microsoft Project and TILOS as inputs and putting the daily re-planning where the planners already worked.

Select a step to see what happened.

Discover · Map the levers

We sat with planners and site engineers to trace how a field change became a schedule change.

  • Listed every lever per stretch and stage: start, duration, shift and factor.
  • Mapped the links: foundation curing before erection, full erection before stringing, one gang after another.
  • Timed a typical re-plan: about 12 hours across Microsoft Project and TILOS.

Deliverable: Lever map and dependency rules.

Design · One base sheet

A stretch-wise base sheet per stage, filled from the Microsoft Project baseline.

  • Plan and actual/forecast start and end for foundation, erection and stringing.
  • Rescaling rule: new date = new minimum + (old date − old minimum) × factor.
  • Factor = (planned duration + days added) ÷ planned duration.

Deliverable: Base sheet layout and formula design.

Build · VBA engine and UserForms

VBA does the iterations; planners only touch the form.

  • USER CONTROL form: pick base data, list stretches, choose stages, add or shift days.
  • Stretch Details form: select a stretch, adjust it and see its Gantt chart at once.
  • Iterations run until every downstream date settles; Refresh From Base restores the plan.

Deliverable: Macro-enabled workbook with two UserForms.

Adopt · Field change in, forecast out

Site updates now reach the schedule on the same day.

  • Field engineers report delays by stretch and stage.
  • Planners enter them in the form and share the refreshed Gantt chart.
  • Weekly review compares the forecast against the baseline.

Deliverable: Daily update routine and review pack.

Scale · Every line package

The workbook became a template for new packages.

  • New stretches load from each project's baseline.
  • Gang counts and curing lags set per package.
  • Forecasts fed back to Microsoft Project for the master schedule.

Deliverable: Package template and setup guide.

The Solution

A schedule engine that lives in Excel

VBA performs the iterations, the full tower schedule is visible in a UserForm, and every change is written back to the spreadsheet. Below are the original screens, followed by a working replica you can try.

01 · The workbook, as built

The original screens

Shown as delivered. Click any screen to enlarge it.

Excel base sheet with erection and stringing columns and annotated formula flow

Base sheet. Tower type, span length and stretch name, then plan and actual/forecast dates for each stage. The coloured boxes show how each date is recalculated.

  • 1FIND MIN / FIND MAX of the stretch's plan dates.
  • 2PART A = MIN ACTUAL; PART B = MIN ACTUAL NEW and MAX ACTUAL NEW.
  • 3EACH VALUE is rescaled from MIN ACTUAL using the FACTOR.
  • 4VLOOKUP brings in the stretch's new start (OLD vs NEW DIFF MIN).
  • 5MIN, MAX, MAX−MIN, S−R give the planned and forecast durations.
  • 6FACTOR = (T6 + R6) / T6 from the manual Add/Subtract days, then COPY ACROSS.
USER CONTROL VBA form with Pick Base Data, List Stretches, stage checkboxes and day controls
Stretch Details VBA form with stretch list, start and end dates and a Gantt chart

USER CONTROL (left): Pick Base Data, List Stretches, choose Foundation, Erection or Stringing, then Add/Subtract Days or Shift Days. Refresh From Base resets the plan; Remove Calculations clears the working columns.

Stretch Details (right): pick a stretch such as 7/10-7/11, read its start (13-Apr-2018) and end (23-May-2018), adjust it, and see the Gantt chart from Apr-18 to Jan-19.

02 · Planner's console

Change one stretch and watch the ripple

A working replica of the Stretch Details form on a sample 14-stretch package. Pick a stretch and stage, add or shift days, then press SUBMIT. Or switch on a field event. The VBA-style iterations run until every downstream date settles.

Stretch Details
SELECT STRETCH
—START DATE —END DATE
Gantt
No pending changes.

Field events

VBA iteration log

' Ready. Base data loaded: 14 stretches × 3 stages.
FoundationErectionStringingBaselineDriving pathDate cursor (drag)

Sample package for illustration: stretch names follow the original form; durations, gang counts and lags are indicative. Rules: erection starts once 55% of foundations are done plus 7 days of curing; stringing starts 3 days after a stretch is fully erected; each gang works its stretches in order.

03 · The schedule sheet, live

Every change written back to the spreadsheet

The same columns as the base sheet. Cells flash when an iteration changes them; yellow cells are manual inputs. Click a row to load that stretch into the form.

04 · 3D line corridor

The forecast, built tower by tower

Every tower and span of the sample package, placed along the route with its river, road and rail crossings. Press play to watch foundations, towers and conductors appear on their forecast dates. Changes made in the console show up here too. Drag to orbit, scroll to zoom, click a tower for its dates.

—forecast
Foundations0/69
Towers erected0/69
Spans strung0/68
—

Spans compressed and towers exaggerated in height so the whole package fits on screen. Dates follow the same schedule as the console and table above.

Impact Dashboard

Re-plans in minutes, slippage seen in time

The engine removed the manual cascade, so planners re-forecast every day. The finish-date forecast stopped jumping late in the project.

Hours to re-plan one field change

Where the time went, step by step

Manual stepTotal

Forecast finish date, by report month

A milestone trend chart: flat is good, late jumps are bad

Forecast finishBaseline finish

Figures indicative; validated metrics available on request.

Before vs After

How the planning team works now

DimensionBeforeAfter
Planning toolsMicrosoft Project and TILOS, updated separatelyOne Excel engine, loaded from the Project baseline
Field change to new planAbout 12 hours of manual editsAbout 2 minutes in a UserForm
Knock-on effectsTraced by hand, stage by stageVBA iterates through every stretch and stage
VisibilityPrinted bar charts, out of dateGantt chart per stretch, refreshed on submit
What-ifToo slow to try optionsTest a delay, see the finish date, then decide
BaselineOverwritten with each updateKept; Refresh From Base restores it
Tools & Tech Used

Built where planners already work

Excel base sheet per stage VBA iteration engine VBA UserForms USER CONTROL & Stretch Details Microsoft Project baseline input TILOS time–location input Excel Gantt charts per stretch and stage
Key Takeaways

What any project team can borrow

01

Model the levers, not the dates

Once start, duration, shift and factor are inputs, every date becomes a result. Planners change one lever instead of fifty cells.

02

Let the iterations carry the cascade

Dependencies between stages and gangs are rules, not memory. The engine runs them until the plan settles, every time.

03

Meet planners in Excel

A UserForm on the sheet they already trust was adopted in weeks. New software would have taken a project cycle.